package com.guat.dao;

import com.guat.entity.Article;
import com.guat.entity.Picture;
import com.guat.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PictureDao {
    //获取所有图片
    public List<Picture> getPictureAll() {
        Connection connection = DBUtil.getConnection();
        String sql = "SELECT * from `picture` ";
        List<Picture> list = new ArrayList<>();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                Picture picture = new Picture();
                picture.setPictureId(rs.getInt(1));
                picture.setPictureUrl(rs.getString(2));
                picture.setPictureIofo(rs.getString(3));
                //2023-12-20 11:52:05.0使用字符串截取,把最后的.0去掉
                String str = rs.getString(4);
                String timeStr = str.substring(0, str.length() - 2); // 去掉最后两个字符
                //把改好的时间格式set进对象
                picture.setUploaded(timeStr);
                list.add(picture);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //上传图片
    public boolean addPicture(String url, String info) {
        Connection connection = DBUtil.getConnection();
        String sql = "insert into `picture`(picture_url,picture_info) value('" + url + "','" + info + "')";
        try {
            Statement statement = connection.createStatement();
            //执行sql语句
            statement.execute(sql);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //根据id查询一条图片信息
    public Picture getPictureById(String id){
        Connection connection = DBUtil.getConnection();
        String sql = "SELECT * from `picture` where picture_id = '" + id + "'";
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            Picture picture = new Picture();
            if (rs.next()) {
                picture.setPictureId(rs.getInt(1));
                picture.setPictureUrl(rs.getString(2));
                picture.setPictureIofo(rs.getString(3));
                picture.setUploaded(rs.getString(4));
            }
            return picture;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //删除图片
    public boolean deletePicture(String id) {
        Connection connection = DBUtil.getConnection();
        String sql = "DELETE FROM `picture` WHERE picture_id = '" + id + "'";
        try {
            Statement statement = connection.createStatement();
            //执行sql语句
            statement.execute(sql);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    //根据id获取图片信息填入输入框
    public Picture doUpdatePicture(String id) {
        Connection connection = DBUtil.getConnection();
        String sql = "SELECT * from `picture` where picture_id = '" + id + "'";
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            Picture picture = new Picture();
            if (rs.next()) {
                picture.setPictureId(rs.getInt(1));
                picture.setPictureUrl(rs.getString(2));
                picture.setPictureIofo(rs.getString(3));
                picture.setUploaded(rs.getString(4));
            }
            return picture;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //根据id修改图片简介信息
    public void updatePicture(String id, String picture_info){
        Connection connection=DBUtil.getConnection();
        String sql="update `picture` set picture_info='"+picture_info+"' where picture_id="+id;
        try {
            Statement statement=connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //根据前端传回的值进行模糊查询
    public List<Picture> searchPicture(String value) {
        Connection connection = DBUtil.getConnection();
        String sql = "SELECT * FROM picture WHERE picture_info LIKE ?";
        List<Picture> list = new ArrayList<>();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, "%"+value+"%");
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                Picture picture = new Picture();
                picture.setPictureId(rs.getInt(1));
                picture.setPictureUrl(rs.getString(2));
                picture.setPictureIofo(rs.getString(3));
                //2023-12-20 11:52:05.0使用字符串截取,把最后的.0去掉
                String str = rs.getString(4);
                String timeStr = str.substring(0, str.length() - 2); // 去掉最后两个字符
                picture.setUploaded(timeStr);
                list.add(picture);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
}
